﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace MINERP.DAL.GoodsDelivery
{
    public class BaseInfoDAL
    {
        string conStr = ConfigurationManager.ConnectionStrings["sqlCon"].ToString();

        #region 获取客运站信息
        /// <summary>
        /// 获取客运站信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetStationDT()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select StationId,StationName from DeliverStation";
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 获取货运站信息
        /// <summary>
        /// 获取货运站信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetStationGoodsDT()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select StationGoodsId,StationGoodsNamePhone from DeliverStationGoods";
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 收货站信息
        /// <summary>
        /// 收货站信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetReceiveStation()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select ReceiveStationId,StationNamePhone from receivestation";
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 新增客运站信息
        /// <summary>
        /// 新增客运站信息
        /// </summary>
        /// <param name="stationName"></param>
        public void InsertStation(string stationName)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "insert into DeliverStation(stationName) values('"+stationName+"')";
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 插入货运站信息
        /// <summary>
        /// 插入货运站信息
        /// </summary>
        /// <param name="stationGoodsNamePhone"></param>
        public void InsertStationGoods(string stationGoodsNamePhone)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "insert into DeliverStationGoods(stationGoodsNamePhone) values('"+stationGoodsNamePhone+"')";
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 插入收货站信息
        /// <summary>
        /// 插入收货站信息
        /// </summary>
        /// <param name="stationNamePhone"></param>
        public void InsertReceiveStation(string stationNamePhone)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "insert into ReceiveStation(stationNamePhone) values('" + stationNamePhone + "')";
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 删除客运站
        /// <summary>
        /// 删除客运站
        /// </summary>
        /// <param name="stationId"></param>
        public void DelStation(List<int> stationIds)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "delete from DeliverStation where stationId=";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                con.Open();
                foreach (int stationId in stationIds)
                {
                    string sql2 = sql + stationId;
                    cmd.CommandText = sql2;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 删除货运站及其电话
        /// <summary>
        /// 删除货运站及其电话
        /// </summary>
        /// <param name="stationId"></param>
        public void DelStationGoods(List<int> stationGoodsIds)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "delete from DeliverStationGoods where stationGoodsId=";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                con.Open();
                foreach (int stationGoodsId in stationGoodsIds)
                {
                    string sql2 = sql + stationGoodsId;
                    cmd.CommandText = sql2;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        public void DelReceiveStation(List<int> receiveStationIds)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "delete from receiveStation where receiveStationId=";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                con.Open();
                foreach (int receiveStationId in receiveStationIds)
                {
                    string sql2 = sql + receiveStationId;
                    cmd.CommandText = sql2;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
